USE [master]
GO
/****** Object:  Database [ATM_PM1]    Script Date: 02/01/2012 00:24:45 ******/
IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'ATM_PM1')
DROP DATABASE [ATM_PM1]
CREATE DATABASE [ATM_PM1]
GO
USE [ATM_PM1]
--- create table - TB_PERMISSION ---
GO
CREATE TABLE [TB_PERMISSION]
(
	[Per_ID] INT PRIMARY KEY,
	[Per_Name] VARCHAR(100) NOT NULL
)
--- create table - TB_ADMINISTRATOR ---
GO
CREATE TABLE [TB_ADMINISTRATOR]
(
	[Admin_ID] INT IDENTITY PRIMARY KEY,
	[Per_ID] INT FOREIGN KEY REFERENCES [TB_PERMISSION]([Per_ID]),
	[Admin_Username] VARCHAR(16) NOT NULL,
	[Admin_Password] VARCHAR(40) NOT NULL,
	[Admin_FullName] VARCHAR(40),
	[Admin_DoB] DATETIME,
	[Admin_Gender] BIT,
	[Admin_Address] VARCHAR(300),
	[Admin_Phone] VARCHAR(15),
	[Admin_Email] VARCHAR(40),
	[Admin_Status] BIT DEFAULT('TRUE') NOT NULL,
	[Admin_Delete] BIT DEFAULT('FALSE') NOT NULL,
	[Admin_Delete_Reason] VARCHAR(MAX)
)
--- create table - TB_USER ---
GO
CREATE TABLE [TB_USER]
(
	[User_ID] INT IDENTITY PRIMARY KEY,
	[User_ID_Card_No] VARCHAR(9) UNIQUE,
	[User_FullName] VARCHAR(40),
	[User_DoB] DATETIME,
	[User_Gender] BIT,
	[User_Phone] VARCHAR(15),
	[User_Address] VARCHAR(300),
	[User_Delete] BIT DEFAULT('FALSE'),
	[User_Delete_Reason] VARCHAR(MAX)
)
--- create table - TB_ACCOUNT ---
GO
CREATE TABLE [TB_ACCOUNT]
(
	[Acc_ID] INT IDENTITY PRIMARY KEY,
	[User_ID] INT FOREIGN KEY REFERENCES [TB_USER]([User_ID]),
	[Acc_No] VARCHAR(16) UNIQUE,
	[Acc_PIN] VARCHAR(40) NOT NULL,
	[Acc_Balance] FLOAT NOT NULL,
	[Acc_Status] BIT DEFAULT('TRUE'),
	[Acc_Delete] BIT DEFAULT('FALSE'),
	[Acc_Delete_Reason] VARCHAR(MAX)
)
--- create table - TB_TRANSACTION ---
GO
CREATE TABLE [TB_TRANSACTION]
(
	[Tran_ID] INT IDENTITY PRIMARY KEY,
	[Tran_Type] BIT NOT NULL,
	[Acc_Pri_ID] INT FOREIGN KEY REFERENCES [TB_ACCOUNT]([Acc_ID]),
	[Acc_Sec_ID] INT FOREIGN KEY REFERENCES [TB_ACCOUNT]([Acc_ID]),
	[Admin_ID] INT FOREIGN KEY REFERENCES [TB_ADMINISTRATOR]([Admin_ID]),
	[Tran_Amount] FLOAT NOT NULL,
	[Tran_Balance] FLOAT NOT NULL,
	[Tran_Reason] VARCHAR(MAX),
	[Tran_Date] DATETIME DEFAULT(GETDATE())
)
--- create table - TB_BANK_INFO ---
GO
CREATE TABLE [TB_BANK_INFO]
(
	[Bank_ID] INT PRIMARY KEY,
	[Bank_Name] VARCHAR(40) NOT NULL,
	[Bank_Address] VARCHAR(300) NOT NULL,
	[Bank_Phone] VARCHAR(15),
	[Bank_Fax] VARCHAR(15),
)
--- create table - TB_ATM_INFO ---
GO
CREATE TABLE [TB_ATM_INFO]
(
	[Sys_ID] INT PRIMARY KEY,
	[Sys_WA] FLOAT NOT NULL, -- Maximum amount of withdrawal in a single day
	[Sys_DA] FLOAT NOT NULL, -- Maximum amount of deposit in a single day 
	[Sys_WT] INT NOT NULL, -- Maximum time of withdrawal in a single day
	[Sys_DT] INT NOT NULL, -- Maximum time of deposit in a single day
	[Sys_MAT] FLOAT NOT NULL, -- Maximum amount of per transfer
	[Sys_AIM] FLOAT NOT NULL, -- Amount in multiple of value
	[Sys_FC1] FLOAT NOT NULL, -- Fast cash value level 1
	[Sys_FC2] FLOAT NOT NULL, -- Fast cash value level 2
	[Sys_FC3] FLOAT NOT NULL, -- Fast cash value level 3
	[Sys_FC4] FLOAT NOT NULL, -- Fast cash value level 4
	[Sys_FC5] FLOAT NOT NULL, -- Fast cash value level 5
	[Sys_FC6] FLOAT NOT NULL, -- Fast cash value level 6
)
--- create table - TB_SYSTEM_REPORT ---
GO
CREATE TABLE [TB_SYSTEM_REPORT]
(
	[Rep_ID] INT IDENTITY PRIMARY KEY,
	[Rep_Title] VARCHAR(100),
	[Rep_Description] VARCHAR(MAX),
	[Rep_Time] DATETIME DEFAULT(GETDATE())
)

--- create procedure - sp_check_acc ---
GO
CREATE PROC [sp_check_acc]
	@sAccNo VARCHAR(16)
AS
BEGIN
	SELECT * FROM [TB_USER], [TB_ACCOUNT]
		WHERE	[TB_USER].[User_ID] = TB_ACCOUNT.[User_ID]
			AND	[Acc_No] LIKE @sAccNo
			AND [Acc_Delete] = 'FALSE'
			AND [User_Delete] = 'FALSE'
END

--- create procedure - sp_insert_system_report ---
GO
CREATE PROC [sp_insert_system_report]
	@sTitle VARCHAR(1000),
	@sDescription VARCHAR(MAX) = ''
AS
BEGIN
	INSERT INTO [TB_SYSTEM_REPORT]([Rep_Title], [Rep_Description])
		VALUES(@sTitle, @sDescription)
END
--- create procedure - sp_change_pin ---
GO
CREATE PROC [sp_change_pin]	
	@nAccID INT,
	@sAccPIN VARCHAR(40),
	@nActionAdmin INT = NULL
AS
BEGIN
	UPDATE [TB_ACCOUNT]
		SET [Acc_PIN] = @sAccPIN
		WHERE [Acc_ID] = @nAccID	
	DECLARE @sDes VARCHAR(100)
	IF @nActionAdmin IS NULL
		BEGIN
			SET @sDes = ('Account ID ' 
					+ CONVERT(VARCHAR(10),@nAccID)
					+ ': change PIN')
		END
	ELSE
		BEGIN
			SET @sDes = ('Administrator ID ' 
					+ CONVERT(VARCHAR(10),@nActionAdmin)
					+ ': reset PIN Account ID '
					+ CONVERT(VARCHAR(10),@nAccID))
		END
	EXEC sp_insert_system_report 'Account', @sDes
END
--- create procedure - sp_transaction ---
GO
CREATE PROC [sp_transaction]
	@bType BIT,
	@nAccID INT,
	@fAmount FLOAT,
	@nAdminID INT = NULL,
	@sReason VARCHAR(MAX) = ''
AS
BEGIN
	DECLARE @fTranBalance FLOAT
	DECLARE @fAccBalance FLOAT
	--
	SET @fAccBalance = (SELECT [Acc_Balance]
								 FROM [TB_ACCOUNT]
								 WHERE [Acc_ID] = @nAccID)
	--
	DECLARE @sDes VARCHAR(100)
	
	IF (@bType = 'TRUE')
	BEGIN
		SET @fTranBalance = @fAccBalance - @fAmount
		IF @nAdminID IS NOT NULL
			BEGIN
				SET @sDes = ('Administrator ID ' 
							+ CONVERT(VARCHAR(10),@nAdminID)
							+ ': withdraw $' 
							+ CONVERT(VARCHAR(20),@fAmount)
							+ ' from Account ID '
							+ CONVERT(VARCHAR(10),@nAccID)
							+ ' .Balance: $' 
							+ CONVERT(VARCHAR(10),@fTranBalance))
				EXEC sp_insert_system_report 'Transaction', @sDes
			END
		ELSE
			BEGIN
				SET @sReason = 'ATM System: Cash Withdraw'
				SET @sDes = ('Account ID ' 
							+ CONVERT(VARCHAR(10),@nAccID )
							+ ': withdraw $' 
							+ CONVERT(VARCHAR(20),@fAmount)
							+ ' .Balance: $' + 
							+ CONVERT(VARCHAR(20),@fTranBalance))
				EXEC sp_insert_system_report 'Transaction', @sDes
			END
	END
	ELSE
	BEGIN
		SET @fTranBalance = @fAccBalance + @fAmount	
		IF @nAdminID IS NOT NULL
			BEGIN
				SET @sDes = ('Administrator ID ' 
							+ CONVERT(VARCHAR(10),@nAdminID)
							+ ': deposit $' 
							+ CONVERT(VARCHAR(20),@fAmount)
							+ ' to Account ID '
							+ CONVERT(VARCHAR(10),@nAccID)
							+ ' .Balance: $' 
							+ CONVERT(VARCHAR(20),@fTranBalance))
				EXEC sp_insert_system_report 'Transaction', @sDes
			END
		ELSE
			BEGIN
				SET @sReason = 'ATM System: Cash Deposit'
				SET @sDes = ('Account ID ' 
							+ CONVERT(VARCHAR(10),@nAccID)
							+ ': deposit $' 
							+ CONVERT(VARCHAR(20),@fAmount)
							+ ' .Balance: $' 
							+ CONVERT(VARCHAR(20),@fTranBalance))
				EXEC sp_insert_system_report 'Transaction', @sDes
			END
	END
	--
	INSERT INTO [TB_TRANSACTION]([Tran_Type], [Acc_Pri_ID]
									, [Admin_ID], [Tran_Reason]
									, [Tran_Amount], [Tran_Balance])
		VALUES(@bType, @nAccID
				, @nAdminID, @sReason
				, @fAmount, @fTranBalance)
	--
	UPDATE [TB_ACCOUNT]
		SET [Acc_Balance] = @fTranBalance
		WHERE [Acc_ID] = @nAccID
	
END
--- create procedure - sp_fund_transfer ---
GO
CREATE PROC [sp_fund_transfer]
	@nFromAccID INT,
	@nToAccID INT,
	@fAmount FLOAT,
	@sReason VARCHAR(MAX) = '',
	@nAdminID INT = NULL
	
AS
BEGIN
	DECLARE @fFromAccBalance FLOAT
	DECLARE @fToAccBalance FLOAT
	--
	SET @fFromAccBalance = (SELECT [Acc_Balance] 
									FROM [TB_ACCOUNT] 
									WHERE [Acc_ID] = @nFromAccID)
	SET @fToAccBalance = (SELECT [Acc_Balance] 
									FROM [TB_ACCOUNT]
									WHERE [Acc_ID] = @nToAccID)
	--
	DECLARE @sDes VARCHAR(1000)
	IF @nAdminID IS NOT NULL
		BEGIN
			SET @sDes = ('Administrator ID ' 
						+ CONVERT(VARCHAR(10),@nAdminID)
						+ ': transfer $' 
						+ CONVERT(VARCHAR(20),@fAmount)
						+ ' from Account ID '
						+ CONVERT(VARCHAR(10),@nFromAccID)
						+ ' to Account ID ' 
						+ CONVERT(VARCHAR(10),@nToAccID))
			EXEC sp_insert_system_report 'Transaction', @sDes
		END
	ELSE
		BEGIN
			SET @sReason = ('ATM System : Transfer money from Account ID ' 
						+ CONVERT(VARCHAR(10),@nFromAccID)
						+ ' to Account ID ' 
						+ CONVERT(VARCHAR(10),@nToAccID))
			SET @sDes = ('Account ID ' 
						+ CONVERT(VARCHAR(10),@nFromAccID)
						+ ': transfer $' 
						+ CONVERT(VARCHAR(20),@fAmount)
						+ ' to Account ID ' 
						+ CONVERT(VARCHAR(10),@nToAccID))
			EXEC sp_insert_system_report 'Transaction', @sDes
		END
	--
	INSERT INTO [TB_TRANSACTION]([Tran_Type], [Acc_Pri_ID]
									, [Acc_Sec_ID], [Admin_ID], [Tran_Reason]
									, [Tran_Amount], [Tran_Balance])
		VALUES('TRUE', @nFromAccID
				, @nToAccID, @nAdminID, @sReason
				, @fAmount, @fFromAccBalance - @fAmount)
	--
	INSERT INTO [TB_TRANSACTION]([Tran_Type], [Acc_Pri_ID]
									, [Acc_Sec_ID], [Admin_ID], [Tran_Reason]
									, [Tran_Amount], [Tran_Balance])
		VALUES('FALSE', @nToAccID
				, @nFromAccID, @nAdminID, @sReason
				, @fAmount, @fToAccBalance + @fAmount)
	--
	UPDATE [TB_ACCOUNT]
		SET [Acc_Balance] = @fFromAccBalance - @fAmount
		WHERE [Acc_ID] = @nFromAccID
	UPDATE [TB_ACCOUNT]
		SET [Acc_Balance] = @fToAccBalance + @fAmount
		WHERE [Acc_ID] = @nToAccID
	
END









--- create procedure - sp_set_status_acc ---
GO
CREATE PROC [sp_set_status_acc]
	@bStatus BIT,
	@nAccID INT,
	@nActionAdmin INT = NULL
AS	
BEGIN
	UPDATE [TB_ACCOUNT]
	SET [Acc_Status] = @bStatus
	WHERE [Acc_ID] = @nAccID
	DECLARE @sDes VARCHAR(100)
	IF @nActionAdmin IS NOT NULL
		BEGIN			
			IF @bStatus = 'TRUE'
				BEGIN
				SET @sDes = ('Administrator ID ' 
								+ CONVERT(VARCHAR(10),@nActionAdmin)
								+ ': set status(Enable) Account ID ' 
								+ CONVERT(VARCHAR(10),@nAccID))
				END
			ELSE
				BEGIN
				SET @sDes = ('Administrator ID ' 
								+ CONVERT(VARCHAR(10),@nActionAdmin)
								+ ': set status(Disable) Account ID ' 
								+ CONVERT(VARCHAR(10),@nAccID))
				END
			EXEC sp_insert_system_report 'System', @sDes
		END
	ELSE
		BEGIN
			SET @sDes = ('Account ID ' 
								+ CONVERT(VARCHAR(10),@nAccID) 
								+ ': set status(Disable) - PIN error')
			EXEC sp_insert_system_report 'Account', @sDes
		END
END



--- create view - view_tran_info ---
GO
CREATE VIEW [view_tran_info]
AS
SELECT [Tran_Type], [Acc_Pri_ID], [Admin_ID], [Tran_Amount], CONVERT(VARCHAR(10), Tran_Date, 101) AS DATE
	FROM [TB_TRANSACTION]
	WHERE [Acc_Sec_Id] IS NULL
--- create procedure - sp_check_account ---
GO
CREATE PROC [sp_check_account]
	@nAccID INT
AS
BEGIN
	DECLARE @nWithdrawCount INT
	DECLARE @nDepositCount INT
	DECLARE @fTotalWithdraw FLOAT
	DECLARE @fTotalDeposit FLOAT
	DECLARE @fBalance FLOAT
	DECLARE @bStatus BIT
	DECLARE @sPIN VARCHAR(40)

	SET @sPIN = (SELECT [Acc_PIN] FROM [TB_ACCOUNT] WHERE [Acc_ID] = @nAccID)
	SET @fBalance = (SELECT [Acc_Balance] FROM [TB_ACCOUNT] WHERE [Acc_ID] = @nAccID)
	SET @bStatus = (SELECT [Acc_Status] FROM [TB_ACCOUNT] WHERE [Acc_ID] = @nAccID)
	SET @nWithdrawCount = (SELECT COUNT([DATE]) 
							FROM [view_tran_info]
							WHERE [DATE] LIKE CONVERT(VARCHAR(10), GETDATE(), 101)
								AND	[Tran_Type] = 'TRUE'
								AND [Acc_Pri_ID] = @nAccID)
	SET @nDepositCount = (SELECT COUNT([DATE]) 
							FROM [view_tran_info] 
							WHERE [DATE] LIKE CONVERT(VARCHAR(10), GETDATE(), 101)
								AND	[Tran_Type] = 'FALSE'
								AND [Acc_Pri_ID] = @nAccID)
	SET @fTotalWithdraw = (SELECT SUM([Tran_Amount])
							FROM [view_tran_info]
							WHERE [DATE] LIKE CONVERT(VARCHAR(10), GETDATE(), 101)
								AND	[Tran_Type] = 'TRUE'
								AND [Acc_Pri_ID] = @nAccID)
	SET @fTotalDeposit = (SELECT SUM([Tran_Amount])
							FROM [view_tran_info] 
							WHERE [DATE] LIKE CONVERT(VARCHAR(10), GETDATE(), 101)
								AND	[Tran_Type] = 'FALSE'
								AND [Acc_Pri_ID] = @nAccID)
	SELECT	@nWithdrawCount AS [Withdraw_Count], 
			@nDepositCount AS [Deposit_Count], 
			@fTotalWithdraw AS [Total_Withdraw_Amount], 
			@fTotalDeposit AS [Total_Deposit_Amount],
			@fBalance AS [Acc_Balance],
			@bStatus AS [Acc_Status],
			@sPIN AS [Acc_PIN]
END

--- create view - view_account_info ---
GO
CREATE VIEW view_account_info
AS
	SELECT [Acc_ID], [Acc_No], [Acc_PIN], [Acc_Balance]
			, [Acc_Status], [TB_USER].[User_ID], [User_FullName]
			, CONVERT(VARCHAR(11),[User_DoB]) AS DoB
			, [User_DoB], [User_Gender], [User_Address]
			, [User_Phone], [User_ID_Card_No], [User_Delete], [Acc_Delete]
		FROM [TB_ACCOUNT], [TB_USER]
		WHERE	[TB_ACCOUNT].[User_ID] = [TB_USER].[User_ID]			







--- create procedure - sp_get_total_amount ---
GO
CREATE PROC sp_get_total_amount
	@nType INT,
	@dFromDate DATETIME,
	@dToDate DATETIME
AS
BEGIN
	IF @nType = 0
	BEGIN
		SELECT SUM([Tran_Amount])/2 AS [Total] FROM [TB_TRANSACTION]
			WHERE	[Acc_Sec_ID] IS NOT NULL
				AND  [Tran_Date] BETWEEN @dFromDate AND @dToDate
	END
	IF @nType = 1
	BEGIN
		SELECT SUM([Tran_Amount]) AS [Total] FROM [TB_TRANSACTION]
			WHERE	[Tran_Type] = 'TRUE'
				AND [Acc_Sec_ID] IS NULL
				AND  [Tran_Date] BETWEEN @dFromDate AND @dToDate
	END
	IF @nType = 2
	BEGIN
		SELECT SUM([Tran_Amount]) AS [Total] FROM [TB_TRANSACTION]
			WHERE	[Tran_Type] = 'FALSE'
				AND [Acc_Sec_ID] IS NULL
				AND  [Tran_Date] BETWEEN @dFromDate AND @dToDate
	END
END
--- create procedure - sp_get_count_tran ---
GO
CREATE PROC sp_get_count_tran
	@nType INT,
	@dFromDate DATETIME,
	@dToDate DATETIME
AS
BEGIN
	IF @nType = 0
	BEGIN
		SELECT COUNT(Tran_ID)/2 AS [Count] FROM [TB_TRANSACTION]
			WHERE	[Acc_Sec_ID] IS NOT NULL
				AND  [Tran_Date] BETWEEN @dFromDate AND @dToDate
	END
	IF @nType = 1
	BEGIN
		SELECT COUNT(Tran_ID) AS [Count] FROM [TB_TRANSACTION]
			WHERE	[Tran_Type] = 'TRUE'
				AND [Acc_Sec_ID] IS NULL
				AND  [Tran_Date] BETWEEN @dFromDate AND @dToDate
	END
	IF @nType = 2
	BEGIN
		SELECT COUNT(Tran_ID) AS [Count] FROM [TB_TRANSACTION]
			WHERE	[Tran_Type] = 'FALSE'
				AND [Acc_Sec_ID] IS NULL
				AND  [Tran_Date] BETWEEN @dFromDate AND @dToDate
	END
END

--- create procedure - sp_create_new_user ---
GO
CREATE PROC sp_create_new_user
	@nActionAdmin INT,
	@sAccNo VARCHAR(16),
	@sPIN VARCHAR(40),
	@fBalance FLOAT,
	@sFullName VARCHAR(40),
	@sIdCardNo VARCHAR(9),
	@bGender BIT,
	@dDoB DATETIME,	
	@sPhone VARCHAR(15),
	@sAddress VARCHAR(300)
AS
BEGIN
	INSERT INTO TB_USER([User_ID_Card_No], [User_FullName], [User_DoB],
							[User_Gender], [User_Phone], [User_Address])
		VALUES (@sIdCardNo, @sFullName, @dDoB,
					@bGender, @sPhone, @sAddress)
	
	DECLARE @nUserID INT
	SET @nUserID = (SELECT [User_ID] FROM [TB_USER] WHERE [User_ID_Card_No] = @sIdCardNo)

	INSERT INTO TB_ACCOUNT([User_ID], [Acc_No], [Acc_PIN], [Acc_Balance])
		VALUES(@nUserID, @sAccNo, @sPIN, @fBalance)
	DECLARE @nAccID INT
	SET @nAccID = (SELECT [Acc_ID] FROM [TB_ACCOUNT] WHERE [User_ID] = @nUserID)
	DECLARE @sDes VARCHAR(100)
	SET @sDes = ('Administrator ID ' 
					+ CONVERT(VARCHAR(10),@nActionAdmin)
					+ ': create new User ID ' 
					+ CONVERT(VARCHAR(10),@nUserID)
					+ ' (Account ID ' 
					+ CONVERT(VARCHAR(10),@nAccID) + ')')
	EXEC sp_insert_system_report 'System', @sDes
END
--- create procedure - sp_insert_atm_info ---
GO
CREATE PROC sp_insert_atm_info
	@nID INT, @fWA FLOAT,	@fDA FLOAT, @nWT INT, @nDT INT, @fMAT FLOAT,
	@fAIM FLOAT, @fFC1 FLOAT, @fFC2 FLOAT, @fFC3 FLOAT,
	@fFC4 FLOAT, @fFC5 FLOAT, @fFC6 FLOAT
AS
BEGIN
	INSERT INTO [TB_ATM_INFO]
		VALUES(@nID, @fWA, @fDA, @nWT, @nDT, @fMAT, @fAIM
					, @fFC1, @fFC2, @fFC3, @fFC4, @fFC5, @fFC6)
END
--- create procedure - sp_insert_bank_info ---
GO
CREATE PROC sp_insert_bank_info
	@nID INT,
	@sName VARCHAR(40),
	@sAddress VARCHAR(300),
	@sPhone VARCHAR(15),
	@sFax VARCHAR(15)
AS
BEGIN
	INSERT INTO [TB_BANK_INFO]
		VALUES(@nID, @sName, @sAddress, @sPhone, @sFax)
END
--- create procedure - sp_view_10_last_tran ---
GO
CREATE PROC sp_view_10_last_tran
	@nAccID INT
AS
BEGIN
	SELECT TOP 10 [Tran_Type], [Tran_Amount], [Tran_Balance], Convert(VARCHAR(20), [Tran_Date], 120) AS [Date]
		FROM [TB_TRANSACTION]
		WHERE [Acc_Pri_ID] = @nAccID
		ORDER BY [Tran_Date] DESC
END

--- unused - unused ---
GO
INSERT INTO TB_PERMISSION VALUES (1, 'Administrator')
GO
INSERT INTO TB_PERMISSION VALUES (2, 'Manager')
GO
sp_insert_atm_info 1, 25000, 25000, 5, 5, 100000, 20, 20, 50, 100, 150, 300, 500
GO
sp_insert_bank_info 1, 'NUCE Bank', '55 giai phong, hai ba trung , Ha Noi, Viet Nam','0412345678','0412345678'
GO

sp_create_new_user 1, '0000000000000000', '4a6cec44464e4022ac28cca8642c8a', 10000, 'Dang Sy Quynh', '0123456789', 'TRUE', '05-17-1990', '0123456789', 'Ha Noi, Viet Nam'